"""
数据库操作模块
"""
import os
import logging
import mysql.connector
from mysql.connector import Error

# 配置日志
logging.basicConfig(
    level=logging.DEBUG,
    format='%(asctime)s - %(levelname)s - %(message)s',
    filename='db_errors.log'
)

class DatabaseManager:
    def __init__(self):
        """初始化数据库管理器"""
        self.connection = None
        self.cursor = None
        self.connect()
        if self.connection:
            self.create_tables_if_not_exist()

    def connect(self):
        """连接到数据库"""
        try:
            from config import DB_CONFIG
            logging.info(f"尝试使用配置连接数据库: {DB_CONFIG}")
            self.connection = mysql.connector.connect(**DB_CONFIG)
            self.cursor = self.connection.cursor(dictionary=True)
            logging.info("数据库连接成功")
            return True
        except Error as e:
            logging.error(f"数据库连接失败: {e}")
            logging.debug("详细连接信息:", exc_info=True)
            return False

    def close(self):
        """关闭数据库连接"""
        if self.cursor:
            self.cursor.close()
        if self.connection and self.connection.is_connected():
            self.connection.close()
            
    def create_tables_if_not_exist(self):
        """确保数据库表存在"""
        try:
            query = """
            CREATE TABLE IF NOT EXISTS password_entries (
                id INT AUTO_INCREMENT PRIMARY KEY,
                platform VARCHAR(100) NOT NULL,
                url VARCHAR(255),
                username VARCHAR(100),
                password TEXT,
                notes TEXT,
                UNIQUE KEY unique_platform_username (platform, username)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            """
            self.cursor.execute(query)
            self.connection.commit()
            return True
        except Error as e:
            print(f"创建表失败: {e}")
            return False

    def add_password(self, platform, url, username, password, notes=""):
        """
        添加新的密码条目
        返回新添加条目的ID
        """
        try:
            query = """
            INSERT INTO password_entries (platform, url, username, password, notes)
            VALUES (%s, %s, %s, %s, %s)
            """
            self.cursor.execute(query, (platform, url, username, password, notes))
            self.connection.commit()
            return self.cursor.lastrowid
        except Error as e:
            print(f"添加密码失败: {e}")
            return False

    def get_all_platforms(self):
        """
        获取所有平台列表
        """
        try:
            # 检查连接是否断开，如果断开则重新连接
            if not self.connection or not self.connection.is_connected():
                logging.info("数据库连接已断开，尝试重新连接")
                self.connect()
                if not self.connection:
                    logging.error("重新连接数据库失败")
                    return []

            logging.info("尝试获取所有平台列表")
            query = "SELECT DISTINCT platform FROM password_entries ORDER BY platform"
            logging.debug(f"执行SQL查询: {query}")
            
            # 重新初始化游标
            self.cursor = self.connection.cursor(dictionary=True)
            self.cursor.execute(query)
            
            # 获取查询结果
            result = self.cursor.fetchall()
            logging.debug(f"查询结果: {result}")
            
            if not result:
                logging.info("数据库中没有找到任何平台")
                return []
            
            # 提取平台名称
            platforms = [row['platform'] for row in result]
            logging.info(f"成功获取平台列表: {platforms}")
            return platforms
        except Error as e:
            logging.error(f"获取平台列表失败: {e}", exc_info=True)
            print(f"获取平台列表失败: {e}")
            return []
            
    def get_all_passwords(self):
        """
        获取所有密码条目
        """
        try:
            query = "SELECT id, platform, url, username, password, notes FROM password_entries ORDER BY platform, username"
            self.cursor.execute(query)
            return self.cursor.fetchall()
        except Error as e:
            print(f"获取所有密码失败: {e}")
            return []
            
    def get_passwords_by_platform(self, platform):
        """
        获取特定平台的所有密码条目
        """
        try:
            query = "SELECT * FROM password_entries WHERE platform = %s"
            self.cursor.execute(query, (platform,))
            return self.cursor.fetchall()
        except Error as e:
            print(f"获取密码失败: {e}")
            return []

    def update_password(self, id, update_data=None):
        """
        更新密码条目的信息
        
        参数:
        - id: 要更新的密码条目ID
        - update_data: 包含要更新字段的字典，例如 {'platform': '新平台', 'password': '新密码'}
        """
        try:
            if not update_data:
                return False
                
            # 构建动态更新查询
            set_clause = ", ".join([f"{field} = %s" for field in update_data.keys()])
            query = f"UPDATE password_entries SET {set_clause} WHERE id = %s"
            
            # 构建参数值列表
            values = list(update_data.values())
            values.append(id)  # 添加WHERE条件的ID值
            
            self.cursor.execute(query, values)
            self.connection.commit()
            return True
        except Error as e:
            print(f"更新密码失败: {e}")
            return False

    def delete_password(self, id):
        """
        删除密码条目
        """
        try:
            query = "DELETE FROM password_entries WHERE id = %s"
            self.cursor.execute(query, (id,))
            self.connection.commit()
            return True
        except Error as e:
            print(f"删除密码失败: {e}")
            return False

    def get_password_by_id(self, id):
        """
        通过ID获取单个密码条目
        """
        try:
            query = "SELECT * FROM password_entries WHERE id = %s"
            self.cursor.execute(query, (id,))
            return self.cursor.fetchone()
        except Error as e:
            print(f"获取密码失败: {e}")
            return None
            
    def get_password(self, id):
        """
        获取密码条目（get_password_by_id的别名）
        """
        return self.get_password_by_id(id)